package com.xinchuang.utils;

import org.apache.commons.compress.utils.IOUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.util.Units;

import java.io.*;

public class PoiUtils {

    /**
     * 生成Excel文件
     * @param workbook
     * @param fileName
     * @return
     */
    public static File createExcelFile(Workbook workbook, String filePath, String fileName) {
        OutputStream stream = null;
        File file = null;
        try {
            file = File.createTempFile(fileName, ".xlsx");
            stream = new FileOutputStream(filePath);
            workbook.write(stream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //这里调用了IO工具包控制开关
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(stream);
        }
        return file;
    }

    /**
     * 合并单元格
     *
     * @param sheet
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param cellValue
     */
    public static void mergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, String cellValue) {
        // 合并单元格
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);
        //设置样式
        regionStyle(sheet, region);
        //赋值
        getRow(sheet, firstRow).createCell(firstCol).setCellValue(cellValue);
    }

    /**
     * 获取行
     *
     * @param sheet
     * @param firstRow
     * @return
     */
    public static Row getRow(Sheet sheet, int firstRow) {
        return sheet.getRow(firstRow) == null ? sheet.createRow(firstRow) : sheet.getRow(firstRow);
    }

    /**
     * 设置区域样式
     *
     * @param sheet
     * @param cellAddresses
     */
    public static void regionStyle(Sheet sheet, CellRangeAddress cellAddresses) {
        // 设置合并区域的边框样式
        RegionUtil.setBorderTop(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellAddresses, sheet);
    }

    /**
     * Excel单元格插入图片
     * @param wb
     * @param sheet
     * @param imageUrl
     * @param firstCol
     * @param firstRow
     * @param lastCol
     * @param lastRow
     */
    public static void cellImage(Workbook wb, Sheet sheet, String imageUrl, int firstCol, int firstRow, int lastCol, int lastRow) {
        try {
            // 读取图片
            InputStream is = new FileInputStream(imageUrl);
            byte[] bytes = org.apache.poi.util.IOUtils.toByteArray(is);
            int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
            is.close();

            CreationHelper helper = wb.getCreationHelper();
            // 创建合并单元格
            CellRangeAddress mergedRegion = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            sheet.addMergedRegion(mergedRegion);

            // 创建图像
            Drawing drawing = sheet.createDrawingPatriarch();
            ClientAnchor anchor = helper.createClientAnchor();
            anchor.setCol1(mergedRegion.getFirstColumn());
            anchor.setRow1(mergedRegion.getFirstRow());
            anchor.setCol2(mergedRegion.getLastColumn());
            anchor.setRow2(mergedRegion.getLastRow());
            anchor.setDx1(0);
            anchor.setDy1(0);
            anchor.setDx2(0);
            anchor.setDy2(0);
            Picture pict = drawing.createPicture(anchor, pictureIdx);

            // 计算合并单元格的宽度和高度
            double mergedWidth = 0.0;
            for (int i = mergedRegion.getFirstColumn(); i <= mergedRegion.getLastColumn(); i++) {
                mergedWidth += sheet.getColumnWidth(i) / 256.0 * 7.0;
            }

            double mergedHeight = 0.0;
            for (int i = mergedRegion.getFirstRow(); i <= mergedRegion.getLastRow(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    mergedHeight += row.getHeightInPoints() / 72 * 96;
                } else {
                    mergedHeight += sheet.getDefaultRowHeightInPoints() / 72 * 96;
                }
            }

            // 获取图像的原始大小
            double imageWidth = pict.getImageDimension().getWidth();
            double imageHeight = pict.getImageDimension().getHeight();

            // 计算图像的新大小以适应合并单元格
            double ratio = Math.min(mergedWidth / imageWidth, mergedHeight / imageHeight);
            int newWidth = (int) (imageWidth * ratio);
            int newHeight = (int) (imageHeight * ratio);

            // 设置图像的新大小
            anchor.setDx2(Units.toEMU(newWidth));
            anchor.setDy2(Units.toEMU(newHeight));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}